HW#3 --- last modified March 02 2019 21:17:30..

Solution set.

Due date: Apr 6

Files to be submitted:
  Hw3Problem.pdf
  DataGenerator.java
  Spool.txt

Purpose: To gain experience with multidimensional indexes and query execution.

Specification:

First, do the following problems from the book and submit them in the file Hw3Problems.pdf:

5.3.8 - Place the following data into a quad tree with dimensions Speed and Ram. Assume the range for Speed is 100 to 500, and for Ram is 0 to 256.

6.4.3 Suppose B(R)=B(S)=10,000. What value of M would we need to compute ( R natural join S ) using the nested-loop algorithm with no more than (a) 100,000, (b) 25,000, (c) 15,000 disk I/Os?

7.1.2 - Add to the rules of <Condition> to allow for the following features of SQL conditionals: (a) Logical operators OR and NOT. (b) Comparisons other than =. (c) Parenthesized conditions. (d) EXISTS expressions.

For the coding part of the homework you need to experiment with Oracle's explain plan command. This useful tutorial on the subject should help you get started. (You should go to evolt anyway to stock up on your favourite old browsers!) To do the experiments you will first need to create a program DataGenerator.java. This program will be run from the command line with a line with the following format:

java DataGenerator start_value num wrap_number txtfile

The program should output into txtfile num many rows of two columns, space separated. The first column starts with the value start_value and increments one with each row. The second column starts with 1 counts up to wrap_number then starts over again at 1, cycling over and over. For example, filling in these values we might write:

java DataGenerator 10 20 5 data.txt

The program should output into data.txt the rows:

10 1
11 2
12 3
13 4
14 5
15 1
16 2
17 3
18 4
19 5
20 1
21 2
22 3
23 4
24 5
25 1
26 2
27 3
28 4
29 5

The outputs of your experiments should be collected in the file Spool.txt. To begin you should create five tables R1(A,B), R2(C,B), R3(D,B), R4(E,B), R5(F,B). All of the columns be of integer type. You should spool the results of the create tables. Next generate five tables worth of data with the following lines:

java DataGenerator 0 1000 100 R1data.txt
java DataGenerator 1000 1000 100 R2data.txt
java DataGenerator 2000 1000 100 R3data.txt
java DataGenerator 3000 1000 100 R4data.txt
java DataGenerator 4000 1000 100 R5data.txt

Use the bulk loader to load these five files into their corresponding table using the bulk loader. Copy the log files into your Spool.txt as well. Now consider the join of all five tables with the condition R1.B=R2.B and R2.B=R3.B and R3.B=R4.B and R4.B=R5.B and R5.B=51. Express this query in SQL in at least 5 different ways. For each way, use explain plan to find out how Oracle would execute the query. Put this information into Spool.txt as well. Try executing each of your equivalent queries and check that the results match (you don't have to put this into Spool.txt). Write up, again in Spool.txt, which you think is the best way to do the query. If you desire you can also experiment with indexes to see if it helps the execution speed.

Point Breakdown